package com.qihoo.finance.lcs.exceltools;

import com.alibaba.fastjson.JSON;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileInputStream;
import java.math.BigDecimal;
import java.util.*;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

public class Read360JIETIAOExcel {
    private static final Pattern NUMBER_PATTERN = Pattern.compile("^([+-]?(([0-9]+)([.]([0-9]+))?|([.]([0-9]+))?))$");

    private static String def_pre_fix = "CAP36";
    public static void main(String[] args) {
        readExcel("D:\\work\\38.资金分发-费率优先级需求\\定价基表_20201221-cap差异化-E24.xlsx", 9);
    }

    private static Logger logger = LoggerFactory.getLogger(Read360JIETIAOExcel.class);

    /**
     * 读取Excel文件内容
     *
     * @param fileName 要读取的Excel文件所在路径
     * @return 读取结果列表，读取失败时返回null
     */
    public static void readExcel(String fileName, int firstRowNum) {
        try (FileInputStream inputStream = new FileInputStream(fileName);
             Workbook workbook = new XSSFWorkbook(inputStream)) {
            // 读取excel中的数据
            parseExcelEXL(workbook, 16, 40);
            parseExcelDSF(workbook, firstRowNum);
        } catch (Exception e) {
            logger.error("解析Excel失败，文件名：" + fileName + " 错误信息：");
            e.printStackTrace();
        }
    }

    private static void parseExcelDSF(Workbook workbook, int firstRowNum) {
        // 解析sheet
        Sheet sheet = workbook.getSheetAt(1);

        // 校验sheet是否合法
        if (sheet == null) {
            return;
        }

        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        // 获取第一行数据
        int lastRowNum = 11;
        Row identifySuffixRow = sheet.getRow(lastRowNum - 4);
        String identifySuffix = null;
        if(Objects.nonNull(identifySuffixRow)){
            identifySuffix = convertCellValueToString(identifySuffixRow.getCell(1), evaluator);
        }
        if(StringUtils.isEmpty(identifySuffix)){
            logger.error("解析Excel失败，标识字符串获取为空");
            return;
        }
        final String identifySuffixFinal = identifySuffix;

        Row firstRow = sheet.getRow(firstRowNum);
        if (null == firstRow) {
            logger.error("解析Excel失败，在第一行没有读取到任何数据！");
            return;
        }

        Map<String, LPR> map = new TreeMap<>();
        for (int d = 0; d < firstRow.getLastCellNum(); d++) {
            String custSeg = convertCellValueToString(firstRow.getCell(d), evaluator);
            LPR lpr;
            if (StringUtils.isNotBlank(custSeg) && (custSeg.startsWith("D") || custSeg.startsWith("C"))) {
                for (int r = firstRowNum + 1; r <= lastRowNum; r++) {
                    Row feeRow = sheet.getRow(r);
                    String term = convertCellValueToString(feeRow.getCell(1), evaluator);
                    if (StringUtils.isNotBlank(term) && isNumeric(term)) {
                        lpr = map.getOrDefault(custSeg + ":" + term, new LPR().setTerm(term).setCustSeg(custSeg));
                        if (StringUtils.isBlank(lpr.getTermFee())) {
                            lpr.setTermFee(convertCellValueToString(feeRow.getCell(d), evaluator));
                        } else if (StringUtils.isBlank(lpr.getNameApr())) {
                            lpr.setNameApr(convertCellValueToString(feeRow.getCell(d), evaluator));
                            lpr.setIRR(lpr.getNameApr());
                            lpr.setRealApr(lpr.getNameApr());
                        }
                        map.put(custSeg + ":" + term, lpr);
                    }
                }
            }
        }
        List<String> list = map.keySet().stream().filter(s -> s.startsWith("C")).collect(Collectors.toList());
        Map<String, LPR> cap = new HashMap<>(4);
        list.forEach(s -> {
            LPR lpr = map.remove(s);
            cap.put(lpr.getTerm(), lpr);
        });
        System.out.println("insert into pd_product_lpr(lpr_code, term, unit_rate, realistic_apr, irr, nominal_apr,cap_unit_rate,cap_realistic_apr,cap_irr,cap_nominal_apr,official_lpr) values ");
        System.out.println(map.values().stream()
                .map(lpr -> String.format("('%s%s:%s:%s', %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", "DFS", StringUtils.right(lpr.getCustSeg(), 2), "CAP36", identifySuffixFinal, lpr.getTerm(), lpr.getTermFee(), lpr.getRealApr(), lpr.getIRR(), lpr.getNameApr()
                        , cap.get(lpr.getTerm()).getTermFee(), cap.get(lpr.getTerm()).getRealApr(), cap.get(lpr.getTerm()).getIRR(), cap.get(lpr.getTerm()).getNameApr(), "0.0385"
                ))
                .collect(Collectors.joining(",")));
        System.out.println(";");
    }


    /**
     * 解析Excel数据
     *
     * @param workbook Excel工作簿对象
     * @return 解析结果
     */
    private static void parseExcelEXL(Workbook workbook, int firstRowNum, int lastRowNum) {
        // 解析sheet
        Sheet sheet = workbook.getSheetAt(1);

        // 校验sheet是否合法
        if (sheet == null) {
            return;
        }

        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        Row identifySuffixRow = sheet.getRow(firstRowNum - 2);
        String identifySuffix = null;
        if(Objects.nonNull(identifySuffixRow)){
            identifySuffix = convertCellValueToString(identifySuffixRow.getCell(1), evaluator);
        }
        if(StringUtils.isEmpty(identifySuffix)){
            logger.error("解析Excel失败，标识字符串获取为空");
            return;
        }
        final String identifySuffixFinal = identifySuffix;

        Row firstRow = sheet.getRow(firstRowNum);
        if (null == firstRow) {
            logger.error("解析Excel失败，在第一行没有读取到任何数据！");
            return;
        }

        Map<String, LPR> map = new TreeMap<>();
        for (int column = 0; column < firstRow.getLastCellNum(); column++) {
            String custSeg = convertCellValueToString(firstRow.getCell(column), evaluator);
            LPR lpr;
            if (StringUtils.isNotBlank(custSeg) && (custSeg.startsWith("E") || custSeg.startsWith("C"))) {
                for (int row = firstRowNum + 1; row <= lastRowNum; row++) {
                    Row feeRow = sheet.getRow(row);
                    if(Objects.isNull(feeRow)){
                        logger.info("null");
                    }
                    String term = convertCellValueToString(feeRow.getCell(1), evaluator);
                    if (StringUtils.isNotBlank(term) && isNumeric(term)) {
                        lpr = map.getOrDefault(custSeg + ":" + term, new LPR().setTerm(term).setCustSeg(custSeg));
                        if (StringUtils.isBlank(lpr.getTermFee())) {
                            lpr.setTermFee(convertCellValueToString(feeRow.getCell(column), evaluator));
                        } else if (StringUtils.isBlank(lpr.getNameApr())) {
                            lpr.setNameApr(convertCellValueToString(feeRow.getCell(column), evaluator));
                        } else if (StringUtils.isBlank(lpr.getIRR())) {
                            lpr.setIRR(convertCellValueToString(feeRow.getCell(column), evaluator));
                        } else if (StringUtils.isBlank(lpr.getRealApr())) {
                            lpr.setRealApr(convertCellValueToString(feeRow.getCell(column), evaluator));
                        }
                        map.put(custSeg + ":" + term, lpr);
                    }
                }
            }
        }
        List<String> list = map.keySet().stream().filter(s -> s.startsWith("C")).collect(Collectors.toList());
        Map<String, LPR> cap = new HashMap<>(4);
        list.forEach(s -> {
            LPR lpr = map.remove(s);
            cap.put(lpr.getTerm(), lpr);
        });
        System.out.println("insert into pd_product_lpr(lpr_code, term, unit_rate, realistic_apr, irr, nominal_apr,cap_unit_rate,cap_realistic_apr,cap_irr,cap_nominal_apr,official_lpr) values ");
        System.out.println(map.values().stream()
                .map(lpr -> String.format("('%s%s:%s:%s', %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", "ELX", StringUtils.right(lpr.getCustSeg(), 2), def_pre_fix, identifySuffixFinal, lpr.getTerm(), lpr.getTermFee(), lpr.getRealApr(), lpr.getIRR(), lpr.getNameApr()
                        , cap.get(lpr.getTerm()).getTermFee(), cap.get(lpr.getTerm()).getRealApr(), cap.get(lpr.getTerm()).getIRR(), cap.get(lpr.getTerm()).getNameApr(), "0.0385"
                ))
                .collect(Collectors.joining(",")));
        System.out.println(";");
    }

    private static class LPR {
        private String term;
        private String termFee;
        private String nameApr;
        private String IRR;
        private String realApr;
        private String custSeg;

        public String getTermFee() {
            return termFee;
        }

        public LPR setTermFee(String termFee) {
            this.termFee = termFee;
            return this;
        }

        public String getTerm() {
            return term;
        }

        public LPR setTerm(String term) {
            this.term = term;
            return this;
        }

        public String getNameApr() {
            return nameApr;
        }

        public LPR setNameApr(String nameApr) {
            this.nameApr = nameApr;
            return this;
        }

        public String getIRR() {
            return IRR;
        }

        public LPR setIRR(String IRR) {
            this.IRR = IRR;
            return this;
        }

        public String getRealApr() {
            return realApr;
        }

        public LPR setRealApr(String realApr) {
            this.realApr = realApr;
            return this;
        }

        public String getCustSeg() {
            return custSeg;
        }

        public LPR setCustSeg(String custSeg) {
            this.custSeg = custSeg;
            return this;
        }

        @Override
        public String toString() {
            return JSON.toJSONString(this);
        }
    }

    /**
     * 将单元格内容转换为字符串
     *
     * @param cell
     * @param evaluator
     * @return
     */
    private static String convertCellValueToString(Cell cell, FormulaEvaluator evaluator) {
        if (cell == null) {
            return null;
        }
        String returnValue = null;
        CellValue cellValue = evaluator.evaluate(cell);
        if (Objects.isNull(cellValue)) {
            return null;
        }
        switch (cellValue.getCellType()) {
            case NUMERIC:   //数字
                // 格式化科学计数法，取一位整数
                returnValue = new BigDecimal(cellValue.getNumberValue()).setScale(8, BigDecimal.ROUND_HALF_UP).stripTrailingZeros().toPlainString();
                break;
            case STRING:    //字符串
                returnValue = cell.getStringCellValue();
                break;
            case BOOLEAN:   //布尔
                Boolean booleanValue = cell.getBooleanCellValue();
                returnValue = booleanValue.toString();
                break;
            case BLANK:     // 空值
                break;
            case FORMULA:   // 公式
                returnValue = cell.getCellFormula();
                break;
            case ERROR:     // 故障
                break;
            default:
                break;
        }
        return returnValue;
    }


    public static Boolean isNumeric(Object obj) {
        if (obj instanceof Number) {
            return Boolean.TRUE;
        }
        if (obj instanceof String) {
            String str = (String) (obj);
            if (StringUtils.isBlank(str)) {
                return Boolean.FALSE;
            }
            return NUMBER_PATTERN.matcher(str).matches();
        }
        return Boolean.FALSE;
    }
}
